var express = require('express');
var Msdb = require("../../../db/Msdb.js");
var router = express.Router();   //可使用 express.Router 类创建模块化、可挂载的路由句柄
const jwt = require('jsonwebtoken');
var Excel = require('exceljs');
var multer = require('multer');
var UUID = require('uuid');
var request = require("request");
const { getxl_util, getnowdate, saveczlog_util, res_util, checkbysql, addformfield, getnowdate_djh, setformfield,
   insert_table, update_table_wstr, update_table_byid, resutdata_to_formdata, resutdata_to_formdata_arr, save_qlczslog_util,
   usequery, 
   getxl_util_free} = require('./server_util.js')

const log = require('../../../logger.js');
const db = require('../../../db/Redisdb.js');
const { getDeviceHistoryParameterByCondition, getDeviceRealParameterByCondition } = require('../../../shzt.js');
var logger = require('../../../logger.js').logger;


//查询销售发票列表
router.post('/xsfp_list', async function (req, res) {
   var rtdata = await usequery(`SELECT xsfp.*, 
       CASE 
           WHEN EXISTS (
               SELECT 1 
               FROM shipSRM_xsfp_mx xsfpmx 
               WHERE xsfpmx.mainid = xsfp.id AND xsfpmx.wlbm IS NULL
           ) THEN '待匹配'
           ELSE '完成'
       END AS status
FROM shipSRM_xsfp xsfp 
 order by id desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询销售发票列表通过id
router.post('/query_xsfp_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_xsfp where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_xsfp_mx where mainid ='${id}' order by id asc`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增销售发票
router.post('/add_xsfp', async function (req, res) {
   let { formdata, mxdata,storage } = req.body
//    let xlm = await getxl_util_free("seq_xsfp")
//    formdata.bh.value = "BOM"+xlm     
   var insrtobj = await insert_table(formdata, "shipSRM_xsfp")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增销售发票', '新增', 'shipSRM_xsfp')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;

   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_xsfp_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增销售发票参数明细', '新增', 'shipSRM_xsfp_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_xsfp_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增销售发票成功`)
})

//更新销售发票
router.post('/update_xsfp', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxjgdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_xsfp")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改销售发票', '修改', 'shipSRM_xsfp')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;

   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_xsfp_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_xsfp_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_xsfp_mx")
      }
   }

   //删除delidarr
   
   await db.update(`delete from shipSRM_xsfp_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_xsfp_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata,  mxdata: rtmxdata }, true, "更新销售发票成功")
})

//  删除销售发票
router.post('/delete_xsfp', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_xsfp where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_xsfp_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除销售发票', '删除', 'shipSRM_xsfp')
   res_util(res, {}, true, "删除成功")
})

//导入excel
router.post('/xsfp_syn', async function (req, res) {
   let { dataExcel, dataMxExcel,storage } = req.body
   var db=new Msdb()
   var formid = await usequery(`select id from shipSRM_xsfp`)
   const formidSet = new Set(formid.map(item => item.id));
   // 过滤 dataExcel，删除符合条件的行
   dataExcel = dataExcel.filter((row, index) => {
      if (formidSet.has(row[3])) {
         // 同时删除 dataMxExcel 中对应的行
         dataMxExcel = dataMxExcel.filter(mxRow => mxRow[3] !== row[3]);
         return false; // 不保留当前行
      }
      return true; // 保留当前行
   });
   // 计算每个 item 的 zsl 值
   const zslMap = new Map();

   dataMxExcel.forEach(mxitem => {
      mxitem[17] = mxitem[17].replace('%', '')
      const key = mxitem[3];
      if (!zslMap.has(key)) {
         zslMap.set(key, 0);
      }
      zslMap.set(key, zslMap.get(key) + parseFloat(mxitem[14]));
   });

   dataExcel.forEach(item => {
      item[1] = zslMap.get(item[3]) || 0;
   });
   var sqlString = ``
   var sqlMxString = ``
   dataExcel.forEach(item => {
      var bz = item.length > 18 ? bz = item[18] : ""
      sqlString += `insert into shipSRM_xsfp(id, create_by, create_time, update_by, update_time, gmf_mc, 
      gmf_nsrsbh, xsf_mc, xsf_nsrsbh, hjje, hjsl, kpr, kpsj, bz,hjse, jshj) values ('${item[3]}','${storage.ygxm}',
      '${item[8]}','${storage.ygxm}','${item[8]}','${item[7]}','${item[6]}','${item[5]}','${item[4]}',
      ${item[9]},${item[1]},'${item[17]}','${item[8]}','${bz}',${item[10]},${item[11]})\n`
   });
   for(var i=0;i<dataMxExcel.length;i++){
      var bz = dataMxExcel[i].length > 26 ? bz = dataMxExcel[i][26] : ""
      sqlMxString += `insert into shipSRM_xsfp_mx(id , create_by, create_time, update_by, update_time, mainid, 
       khwlmc, khggxh, dw, sl ,dj, je, shuil,se, bz) values (next value  for seq_id ,'${storage.ygxm}','${dataMxExcel[i][8]}',
       '${storage.ygxm}','${dataMxExcel[i][8]}','${dataMxExcel[i][3]}','${dataMxExcel[i][11]}',
      '${dataMxExcel[i][12]}','${dataMxExcel[i][13]}',${dataMxExcel[i][14]},${dataMxExcel[i][15]},
      ${dataMxExcel[i][16]},${dataMxExcel[i][17]},${dataMxExcel[i][18]},'${bz}' )\n`
   }
   await db.update(sqlString)
   await db.update(sqlMxString)
   await db.update(`merge into shipSRM_xsfp_mx a using (SELECT ggxh, wlbm, wlmc
      FROM shipSRM_wlda
      WHERE wlbm LIKE '%C%' 
        AND ggxh IS NOT NULL 
        AND ggxh != ''
        AND ggxh IN (
            SELECT ggxh
            FROM shipSRM_wlda
            WHERE wlbm LIKE '%C%' 
              AND ggxh IS NOT NULL 
              AND ggxh != ''
            GROUP BY ggxh
            HAVING COUNT(ggxh) = 1
        )
      )b on a.khggxh=b.ggxh when matched then UPDATE
      set wlmc=b.wlmc,wlbm=b.wlbm,ggxh=b.ggxh;`)
   res_util(res, { }, true, "导入销售发票成功")
})


//查询工票单列表
router.post('/gpd_list', async function (req, res) {
   var rtdata = await usequery(`SELECT * from shipSRM_gpd order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询工票单列表通过id
router.post('/query_gpd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_gpd where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_gpd_mx where mainid ='${id}' order by id asc`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增工票单
router.post('/add_gpd', async function (req, res) {
   let { formdata, mxdata,storage } = req.body
//    let xlm = await getxl_util_free("seq_gpd")
//    formdata.bh.value = "BOM"+xlm     
   var insrtobj = await insert_table(formdata, "shipSRM_gpd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增工票单', '新增', 'shipSRM_gpd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;

   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_gpd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增工票单参数明细', '新增', 'shipSRM_gpd_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_gpd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增工票单成功`)
})

//更新工票单
router.post('/update_gpd', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxjgdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_gpd")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改工票单', '修改', 'shipSRM_gpd')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;

   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_gpd_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_gpd_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_gpd_mx")
      }
   }

   //删除delidarr
   
   await db.update(`delete from shipSRM_gpd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_gpd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata,  mxdata: rtmxdata }, true, "更新工票单成功")
})

//  删除工票单
router.post('/delete_gpd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_gpd where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_gpd_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除工票单', '删除', 'shipSRM_gpd')
   res_util(res, {}, true, "删除成功")
})

//移动端工票单新增
router.post('/add_gpd_mx', async function (req, res) {
   let {  mxdata,storage,formdata } = req.body
   var db = new Msdb()
   var id = await getxl_util()
   let sql = `INSERT into shipSRM_gpd (id,cjzr,scr,scrq,sscj,create_time,create_by,update_time,update_by)VALUES
   ('${id}','${formdata.cjzr.value}','${formdata.scr.value}','${formdata.scrq.value}','${formdata.sscj.value}',
   getdate(),'${storage.ygxm}',getdate(),'${storage.ygxm}')`
   await db.update(sql)
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var idmx = await getxl_util()
      var sqlString = `insert into shipSRM_gpd_mx(id,mainid,create_time,create_by,update_time,update_by,wlbm,wlmc,ggxh,
      dj,xj,wgsl,wggs,bz,gx)VALUES ('${idmx}','${id}',getdate(),'${storage.ygxm}',getdate(),'${storage.ygxm}','${mxitem.wlbm}',
      '${mxitem.wlmc}','${mxitem.ggxh}',${mxitem.dj},${mxitem.xj},${mxitem.wgsl},${mxitem.wggs},'${mxitem.bz}','${mxitem.gx}')`
         await db.update(sqlString)
   }
   res_util(res, {}, true, "新增成功")
})

module.exports = router; 
